﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

namespace Utility
{
   
    public class Connection
    {
        private SqlDataAdapter myAdapter;
        private SqlConnection conn;

      
        public Connection()
        {
            myAdapter = new SqlDataAdapter();
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings
                    ["QuanLyPhongHocConnectionString"].ConnectionString);
        }

        /// <method>
        /// Open Database Connection if Closed or Broken
        /// </method>
        private SqlConnection openConnection()
        {
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken )
            {
                conn.Open();
                
            }
            return conn;
        }

        public void CloseConnectData()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                //conn.Dispose();
                //myAdapter.Dispose();
                //conn = null;
            }
        }

        /// <method>
        /// Select Query : THUC THI CAU LENH SELECT CO THAM SO
        /// </method>
        public DataTable executeSelectQueryWithParams(String _query, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            DataTable dataTable = new DataTable();
            dataTable = null;
            DataSet ds = new DataSet();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myCommand.ExecuteNonQuery();                
                myAdapter.SelectCommand = myCommand;
                myAdapter.Fill(ds);
                dataTable = ds.Tables[0];
            }
            catch (SqlException e)
            {
                Console.Write("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
                return null;
            }
            finally
            {
                CloseConnectData();
            }
            return dataTable;
        }

    //*******************************************************************************************************************************************

        

    //****************************THUC THI CAU LENH SELECT KHONG CO THAM SO*********************************************

    public DataTable executeSelectQuery(String _query)
    {
        SqlCommand myCommand = new SqlCommand();
        DataTable dataTable = new DataTable();
        dataTable = null;
        DataSet ds = new DataSet();
        try
        {
            myCommand.Connection = openConnection();
            myCommand.CommandText = _query;
            myCommand.ExecuteNonQuery();
            myAdapter.SelectCommand = myCommand;
            myAdapter.Fill(ds);
            dataTable = ds.Tables[0];
        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
            return null;
        }
        finally
        {
            CloseConnectData();
        }
        return dataTable;
    }


    }
}
